﻿CREATE PROCEDURE [dbo].[DublicateDatabase]
	@dbName NVARCHAR(200)
	,	@folder NVARCHAR(2000) = 'D:\ya-db\'
AS
BEGIN
	IF NOT EXISTS(SELECT * FROM [master].[dbo].[sysdatabases] WHERE name = @dbName)
	BEGIN
		PRINT 'Database ' + @dbName + ' not exist'
		RETURN
	END


	DECLARE @tempTable TABLE
	(
		[type]	TINYINT	NULL,
		[physical_name]	NVARCHAR(MAX)	NULL
	)

	INSERT INTO @tempTable
	SELECT f.[type], f.[physical_name]
	FROM [master].[sys].[master_files] AS f
	INNER JOIN [master]..[sysdatabases] AS db ON db.[dbid] = f.[database_id]
	WHERE db.[name] = 'Ext'

	IF (SELECT COUNT(*) FROM @tempTable) = 0
	BEGIN
		PRINT 'Database ' + @dbName + ' files not founded'
		RETURN
	END



	-- the new database name
	DECLARE	@copyDbName NVARCHAR(200) = @dbName + '.Copy'

	EXEC [dbo].[DropDatabase] @dbName = @copyDbName

	DECLARE @backupFile NVARCHAR(2000) = @folder + @dbName + '.bak'
	EXEC [dbo].[BackupDatabase] @dbName = @dbName, @backupFile = @backupFile

	BEGIN
		EXEC dbo.RestoreBackup @backupFile = @backupFile, @dbName = @copyDbName, @dbFolder = @folder
		EXEC [dbo].[DetachDatabase] @dbName = @copyDbName
	END

	RETURN
	--Cannot detach the database 'Ext' because it is currently in use.
	EXEC [dbo].[DetachDatabase] @dbName = @dbName

	EXEC dbo.RestoreBackup @backupFile = @backupFile, @dbName = @copyDbName, @dbFolder = @folder

	EXEC [dbo].[RenameDatabase] @oldDbName = @copyDbName, @newDbName = @dbName

	EXEC [dbo].[DetachDatabase] @dbName = @dbName


	DECLARE @query varchar(MAX) = 'CREATE DATABASE [' + @dbName + '] ON '

	DECLARE @CURSOR CURSOR
	DECLARE @FILE	NVARCHAR(MAX)

	SET @CURSOR  = CURSOR SCROLL
	FOR
	SELECT [physical_name] FROM @tempTable
	OPEN @CURSOR
	FETCH NEXT FROM @CURSOR INTO @FILE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @query = @query + ' ( FILENAME = N''' + @FILE + ''' ), '
		FETCH NEXT FROM @CURSOR INTO @FILE
	END
	CLOSE @CURSOR		

	SET @query = SUBSTRING ( @query , 0, LEN(@query)) + 'FOR ATTACH'
	EXEC (@query)

END